const { db, sqlExec } = require('../db/mysql57');

const jobDao = {
    /*
     * 获取所有职位
     */
    async getAllJob(uid, lastJobId) {
        let sql = `SELECT
                j.id as id,
                c.icon as cicon,
                c.name as cname,
                c.tag as ctag,
                j.name as jname,
                j.money as money,
                j.tags as jtags,
                (select name from City where id = j.cityId LIMIT 1) as city,
                j.position as position,
                (select name from Education where id = j.educationId LIMIT 1) as level,
                DATE_FORMAT(j.time, '%Y-%m-%d') as time,
                js.name as status,
                js.textColor as statusTextColor,
                js.bgColor as statusBgColor,
                j.goods as goods,
                j.jobRequirements as jobRequirements,
                (select
                    count(*)
                from ResumeSubmissions as r
                left join Resume as rs on r.resumeId = rs.id
                where r.jobId = j.id and rs.uid = ? and isDel = 'false') as isDeliver,
                (select name from CompanyScale where id = c.scaleId LIMIT 1) as companyCount
            FROM Job as j
            left join Company as c on c.id = j.companyId
            left join JobStatus as js on j.statusId = js.id
            ${lastJobId ? `WHERE j.time < (
                select time from Job where id = ?
            )` : ''}
            ORDER BY j.time DESC
            LIMIT 20`;
        let data = lastJobId ? [uid, lastJobId] : [uid];
        return sqlExec(sql, data);
    },
    /*
     * 获取职位详情
     */
    async getJobDetail(uid, jobId) {
        let sql = `select
                j.id as id,
                c.icon as cicon,
                c.name as cname,
                c.tag as ctag,
                j.name as jname,
                j.money as money,
                j.tags as jtags,
                (select name from City where id = j.cityId LIMIT 1) as city,
                j.position as position,
                (select name from Education where id = j.educationId LIMIT 1) as level,
                DATE_FORMAT(j.time, '%Y-%m-%d') as time,
                js.name as status,
                js.textColor as statusTextColor,
                js.bgColor as statusBgColor,
                j.description as description,
                j.goods as goods,
                j.jobRequirements as jobRequirements,
                (select
                    count(*)
                from ResumeSubmissions as r
                left join Resume as rs on r.resumeId = rs.id
                where r.jobId = j.id and rs.uid = ? and isDel = 'false') as isDeliver,
                (select name from CompanyScale where id = c.scaleId LIMIT 1) as companyCount
            from Job as j
            left join Company as c on c.id = j.companyId
            left join JobStatus as js on j.statusId = js.id
            where j.id = ?`;
        let data = [uid, jobId];
        return sqlExec(sql, data);
    },
    /*
     * 查询是否收藏了职位
     */
    async isCollectJob(uid, jobId) {
        let sql = `select
            count(*) as isCollect
        from JobFavorites
        where uid = ? and jobId = ?
        limit 1`;
        let data = [uid, jobId];
        return sqlExec(sql, data);
    },
    /*
     * 收藏职位
     */
    async collectJob(uid, jobId) {
        let sql = `insert into JobFavorites (uid, jobId) (select ?, ? where not exists (select * from JobFavorites where uid = ? and jobId = ?))`;
        let data = [uid, jobId, uid, jobId];
        return sqlExec(sql, data);
    },
    /*
     * 取消收藏职位
     */
    async delCollectJob(uid, jobId) {
        let sql = `delete from JobFavorites where uid = ? and jobId = ?`;
        let data = [uid, jobId];
        return sqlExec(sql, data);
    },
    /*
     * 添加个人简历接口
     */
    async addResume(uid, name, educationId, position, email, phone, college, studyingTime, major, professional, projExperience, workExperience) {
        let sql = `insert into Resume(
                uid, name, educationId, position,
                email, phone, college, studyingTime,
                major, professional,
                projExperience, workExperience
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        let data = [uid, name, educationId, position, email, phone, college, studyingTime, major, professional, projExperience, workExperience];
        return sqlExec(sql, data);
    },
    /*
     * 查询个人简历接口
     */
    async getResume(uid) {
        let sql = `select
            uid, name, (
                select name from Education as e where e.id = educationId
            ) as eduBackground, position, email,
            phone, college, studyingTime, major, professional,
            projExperience, workExperience
        from Resume
        where uid = ?
        order by createTime DESC
        LIMIT 1`;
        let data = [uid];
        return sqlExec(sql, data);
    },
    // /*
    //  * 修改个人简历接口
    //  */
    // async updateResume(uid, name, eduBackground, position, email, phone, college, studyingTime, major, professional, projExperience, workExperience) {
    //     let sql = `update Resume set name = ?, eduBackground = ?, position = ?, email = ?, phone = ?, college = ?, studyingTime = ?, major = ?, professional = ?, projExperience = ?, workExperience = ? where uid = ?`;
    //     let data = [name, eduBackground, position, email, phone, college, studyingTime, major, professional, projExperience, workExperience, uid];
    //     return sqlExec(sql, data);
    // },
    /*
     * 岗位投递接口
     */
    async resumeSubmission(uid, jobId) {
        let resumeId = null;
        // 尝试获取当前的简历ID
        try {
            let sql = `select id from Resume where uid = ? order by createTime DESC LIMIT 1`;
            let data = [uid];
            let result = await sqlExec(sql, data);
            if (result.length > 0) {
                resumeId = result[0].id;
            } else {
                throw "当前用户不存在简历";
            }
        } catch (err) {
            throw "获取简历失败";
        }
        try {
            let sql = `insert into ResumeSubmissions (jobId, resumeId) (
                    select ?, ?
                        where not exists (
                            select * from ResumeSubmissions where jobId = ? and resumeId = ? and isDel = 'false'
                        )
                    )`;
            let data = [jobId, uid, jobId, uid];
            return sqlExec(sql, data);
        } catch (err) {
            throw "投递失败";
        }
    },
    /*
     * 删除投递记录接口
     */
    async delResumeSubmission(uid, resumeSubmissionId) {
        let sql = `update ResumeSubmissions as rs set rs.isDel = 'true' where rs.id = ? and (
            select uid from Resume as r where r.id = rs.resumeId LIMIT 1
        ) = ?`;
        let data = [resumeSubmissionId, uid];
        return sqlExec(sql, data);
    },
    /*
     * 获取所有投递记录接口
     */
    async getAllResumeSubmission(uid) {
        let sql = `select
            rs.id as id,
            j.name as jname,
            c.name as cname,
            rs.status as status,
            rs.isRefuse as isRefuse,
            DATE_FORMAT(rs.createTime, '%Y-%m-%d %H:%i:%s') as createTime,
            DATE_FORMAT(rs.updateTime, '%Y-%m-%d %H:%i:%s') as updateTime
        from User as u
        left join Resume as r on r.uid = u.id
        left join ResumeSubmissions as rs on rs.resumeId = r.id
        left join Job as j on j.id = rs.jobId
        left join Company as c on c.id = j.companyId
        where u.id = ? and rs.isDel = 'false'`;
        let data = [uid];
        return sqlExec(sql, data);
    }
};

module.exports = jobDao;